/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package SqlDatabase;

import java.io.PrintWriter;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

/**
 *
 * @author Du
 */
public class SqlDatabase {

    private static Connection dbCon; // handles database connection
    private static String dbName;
    private static String fileTablename;
    private static String copyTablename;
    private static String dbLog;
    private static PrintWriter log = null;

    public SqlDatabase() throws ClassNotFoundException, SQLException {
        dbName = "BDArquivo.db";
        dbLog = "BDLog.txt";
        fileTablename = "arquivo";
        copyTablename = "copia";

        openDb();
        configDb();

        createTable();
        createTableCopy();

        //Exemplos de INSERT (fazer em multi-thread)
        create("doc1.txt", 1000, "frontend");
        create("doc2.txt", 124, "frontend");
        create("doc_duplicate.txt", 24, "frontend");

        createCopy("doc_duplicate.txt", "node2");
        createCopy("doc_duplicate.txt", "node5");

        showFiles();
        showCopies();

        //Exemplo de EXISTS
        System.out.println(existsCopy("doc_duplicate.txt", "node2"));

        //Exemplo de BUSCA COPIA por nome (retorna List de hashcodes)
        List files = null;
        files = searchCopy("doc_duplicate.txt");
        System.out.println(files);

        //Exemplo de DELETE de copia
//        for (int i = 0; i < files.size(); i++) {
//            if (i == 0) {
//                deleteCopy((Integer) files.get(i));
//            }
//        }

        showFiles();
        showCopies();

        //Exemplo de UPDATE
        setFlag("doc_duplicate.txt", "node2", "r");
        setFlag("doc1.txt", "frontend", "w");

        showFiles();
        showCopies();

        //Exemplo de 'w' -> '-'
        setFlag("doc1.txt", "frontend", "-");
        updateDateM("doc1.txt");

        //Exemplo de GET FLAG
        System.out.println(getFlag("doc_duplicate.txt"));

        //Exemplo de GET SIZE
        System.out.println(getSize("doc_duplicate.txt"));

        //Exemplo de COUNT copias sendo lidas
        System.out.println(countCopiesBeingRead("doc_duplicate.txt"));

        //Exemplo de DELETE por nome do arquivo
        if (getFlag("doc_duplicate.txt") == "-") {
            delete("doc_duplicate.txt");
        } else if (getFlag("doc_duplicate.txt") == "r") {
            //ESPERAR por countCopiesBeingRead("doc_duplicate.txt") == 0
            //SO ENTAO:
            delete("doc_duplicate.txt");
        } else {
            System.out.println("Impossivel deletar: arquivo 'doc_duplicate.txt' sendo escrito");
        }

        showFiles();
        showCopies();
    }

    @Override
    protected void finalize() throws Throwable {
        try {
            closeDb();        // close database file
        } finally {
            super.finalize();
        }
    }

    public void openDb() throws ClassNotFoundException, SQLException {
        String connector = "jdbc:sqlite:" + dbName;
        try {
            Class.forName("org.sqlite.JDBC");
            dbCon = DriverManager.getConnection(connector);
        } catch (Exception e) {
            System.err.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        System.out.println("Opened database successfully");
    }

    public void configDb() {
        Statement stmt = null;
        try {
            stmt = (Statement) dbCon.createStatement();
            stmt.execute("PRAGMA foreign_keys = ON"); //ENGINE=InnoDB

//            String sql = ".log "
//                    + filename
//                    + ";";
//            stmt.executeUpdate(sql);
            stmt.execute("pragma journal_mode=DELETE"); //http://www.sqlite.org/wal.html
            stmt.close();

            log = new PrintWriter(dbLog, "UTF-8"); //Opens log file
        } catch (Exception e) {
            System.err.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        System.out.println("    Error log enabled");
    }

    public void createTable() {
        Statement stmt = null;
        long startTime = System.currentTimeMillis();
        try {
            stmt = dbCon.createStatement();
            stmt.executeUpdate("DROP TABLE IF EXISTS " + fileTablename + ";");
            String sql = "CREATE TABLE "
                    + fileTablename
                    + "( name         VARCHAR(50) NOT NULL PRIMARY KEY,"
                    + "  size         INTEGER,"
                    + "  dateCreated  DATETIME NOT NULL,"
                    + "  dateModified DATETIME NOT NULL,"
                    + "  flag         CHAR(1) "
                    + " CHECK (flag IN ('r', 'w', '-', 'd'))  )";
            stmt.executeUpdate(sql);
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL Table File successfull: " + duration);
    }

    public void createTableCopy() {
        Statement stmt = null;
        long startTime = System.currentTimeMillis();
        try {
            stmt = dbCon.createStatement();
            stmt.executeUpdate("DROP TABLE IF EXISTS " + copyTablename + ";");
            String sql = "CREATE TABLE "
                    + copyTablename
                    + "( code         INTEGER NOT NULL PRIMARY KEY,"
                    + "  name         VARCHAR(50) NOT NULL,"
                    + "  host         VARCHAR(30) NOT NULL,"
                    + "  flag         CHAR(1),"
                    + " FOREIGN KEY (name) REFERENCES " + fileTablename
                    + " (name) ON DELETE CASCADE ON UPDATE CASCADE,"
                    + " CHECK (flag IN ('r', 'w', '-')) )";
            stmt.executeUpdate(sql);
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL Table Copy successfull: " + duration);
    }

    public void createCopy(String name, String host) {
        PreparedStatement prep = null;
        long startTime = System.currentTimeMillis();
        if (!exists(name)) {
            log.println(getSystemTime() + "SQL New Copy failed: inexistent file");
            return;
        }

        if (existsCopy(name, host)) {
            log.println(getSystemTime() + "SQL New Copy failed: copy already exists in host");
            return;
        }

        try {
            dbCon.setAutoCommit(false);

            prep = dbCon.prepareStatement("INSERT INTO " + copyTablename + " VALUES (?, ?, ?, ?)");
            prep.setString(1, Integer.toString(prep.hashCode()));
            prep.setString(2, name);
            prep.setString(3, host);
            prep.setString(4, "-");

            prep.executeUpdate();

            prep.close();
            dbCon.commit();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;

        log.println(getSystemTime() + "SQL New Copy '" + name + "' to " + host + ": " + duration);
    }

    public void create(String name, int size, String host) {
        PreparedStatement prep = null;
        long startTime = System.currentTimeMillis();
        try {
            dbCon.setAutoCommit(false);

            prep = dbCon.prepareStatement("INSERT INTO " + fileTablename + " VALUES (?, ?, strftime('%s','now'), strftime('%s','now'), ?)");
            prep.setString(1, name);
            prep.setString(2, Integer.toString(size));
            prep.setString(3, "-");

            prep.executeUpdate();

            prep.close();
            dbCon.commit();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL New File '" + name + "' to " + host + ": " + duration);

        createCopy(name, host); // Creates copy in host
    }

    public void showFiles() {
        Statement stmt = null;
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + fileTablename + ";");
            System.out.println("Name                          |Size    |Date Created            |Date Modified           |R/W Flag|");
            while (rs.next()) {
                String name = rs.getString("name");
                int size = rs.getInt("size");

                long unix_time = rs.getLong("dateCreated");
                Timestamp dateC = new Timestamp(unix_time * 1000 + unix_time % 1000);

                unix_time = rs.getLong("dateModified");
                Timestamp dateM = new Timestamp(unix_time * 1000 + unix_time % 1000);

                String flag = rs.getString("flag");

                System.out.println(String.format("%-30s|%7sB|%-24s|%-24s|%-8s|", name, size, dateC, dateM, flag));
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
    }

    public void showCopies() {
        Statement stmt = null;
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + copyTablename + ";");
            System.out.println("Hashcode  |Name                          |Host                          |R/W Flag|");
            while (rs.next()) {
                int code = rs.getInt("code");
                String name = rs.getString("name");
                String host = rs.getString("host");
                String flag = rs.getString("flag");

                System.out.println(String.format("%-10s|%-30s|%-30s|%-8s|", code, name, host, flag));
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
    }

    public List searchCopy(String name) {
        Statement stmt = null;
        List<Integer> out_list = new ArrayList<Integer>();
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + copyTablename
                    + " WHERE name LIKE '" + name + "';");
            while (rs.next()) {
                int code = rs.getInt("code");
                out_list.add(code);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            System.err.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        return out_list;
    }

    public boolean existsCopy(String name, String host) {
        Statement stmt = null;
        boolean out = false;
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + copyTablename
                    + " WHERE name LIKE '" + name
                    + "' AND host LIKE '" + host + "';");

            if (rs.next()) {
                out = true;
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        return out;
    }

    public boolean exists(String filename) {
        Statement stmt = null;
        boolean out = false;
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + fileTablename
                    + " WHERE name LIKE '" + filename + "';");

            if (rs.next()) {
                out = true;
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        return out;
    }

    public void updateDateM(String filename) {
        Statement stmt = null;
        long startTime = System.currentTimeMillis();
        if (!exists(filename)) {
            log.println(getSystemTime() + "SQL Update failed: inexistent file");
            return;
        }

        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            String sql = "UPDATE " + fileTablename
                    + " SET dateModified = strftime('%s','now') WHERE name LIKE '"
                    + filename + "';";
            stmt.executeUpdate(sql);

            dbCon.commit();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL Update successfull: " + duration);
    }

    public void updateSize(String filename, int size) {
        Statement stmt = null;
        long startTime = System.currentTimeMillis();
        if (!exists(filename)) {
            log.println(getSystemTime() + "SQL Update failed: inexistent file");
            return;
        }

        try {
            dbCon.setAutoCommit(false);

            String sql = "UPDATE " + fileTablename
                    + " SET size = " + size
                    + " WHERE name LIKE '" + filename + "';";
            stmt.executeUpdate(sql);

            dbCon.commit();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL Update successfull: " + duration);
    }

    /* Given a filename and a copy host, sets the flag in both File and Copy tables. */
    public void setFlag(String filename, String host, String flag) {
        Statement stmt = null;
        long startTime = System.currentTimeMillis();
        if (!exists(filename)) {
            log.println(getSystemTime() + "SQL Update failed: inexistent file");
            return;
        }

        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            String sql = "UPDATE " + fileTablename
                    + " SET flag = '" + flag
                    + "' WHERE name LIKE '"
                    + filename + "';";
            stmt.executeUpdate(sql);

            sql = "UPDATE " + copyTablename
                    + " SET flag = '" + flag
                    + "' WHERE name LIKE '" + filename
                    + "' AND host LIKE '" + host + "';";
            stmt.executeUpdate(sql);

            dbCon.commit();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL Update successfull: " + duration);
    }

    public void deleteCopy(int code) {
        Statement stmt = null;
        long startTime = System.currentTimeMillis();
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            String sql = "DELETE FROM " + copyTablename + " WHERE code = " + code + ";";
            stmt.executeUpdate(sql);
            dbCon.commit();

            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL Delete Copy " + code + " successfull: " + duration);
    }

    public void deleteCopy(String filename, String host) {
        Statement stmt = null;
        long startTime = System.currentTimeMillis();
        if (!existsCopy(filename, host)) {
            log.println(getSystemTime() + "SQL Delete failed: inexistent copy");
            return;
        }
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            String sql = "DELETE FROM " + copyTablename + " WHERE name LIKE '" + filename
                    + "' AND host LIKE '" + host + "';";
            stmt.executeUpdate(sql);
            dbCon.commit();

            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL Delete Copy '" + filename + "' in " + host + " successfull: " + duration);
    }

    public void delete(String filename) {
        Statement stmt = null;
        long startTime = System.currentTimeMillis();
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            String sql = "DELETE FROM " + fileTablename + " WHERE name LIKE '" + filename + "';";
            stmt.executeUpdate(sql);
            dbCon.commit();

            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        long duration = System.currentTimeMillis() - startTime;
        log.println(getSystemTime() + "SQL Delete File '" + filename + "' successfull: " + duration);
    }

    public String getFlag(String filename) {
        Statement stmt = null;
        String flag = null;
        if (!exists(filename)) {
            log.println(getSystemTime() + "SQL Select failed: inexistent file");
            return null;
        }
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + fileTablename
                    + " WHERE name LIKE '" + filename + "';");

            while (rs.next()) {
                flag = rs.getString("flag");
            }


            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        return flag;
    }

    public int getSize(String filename) {
        Statement stmt = null;
        int size = 0;
        if (!exists(filename)) {
            log.println(getSystemTime() + "SQL Select failed: inexistent file");
            return 0;
        }
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + fileTablename
                    + " WHERE name LIKE '" + filename + "';");

            while (rs.next()) {
                size = rs.getInt("size");
            }


            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        return size;
    }

    public int countCopiesBeingRead(String filename) {
        Statement stmt = null;
        int count = 0;
        try {
            dbCon.setAutoCommit(false);

            stmt = (Statement) dbCon.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + copyTablename
                    + " WHERE name LIKE '" + filename
                    + "' AND flag = 'r' GROUP BY (flag)");

            while (rs.next()) {
                count = rs.getInt("COUNT(*)");
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        return count;
    }

    public void closeDb() {
        try {
            dbCon.close();
            log.close();
        } catch (Exception e) {
            log.println(getSystemTime() + e.getClass().getName() + ": " + e.getMessage());
            closeDb();
            System.exit(0);
        }
        System.out.println("Closed database");
    }

    public String getSystemTime() {
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss ");
        Date date = new Date();
        return dateFormat.format(date);
    }
}
